package cn.bestick.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.bestick.model.DataDic;
import cn.bestick.model.PageBean;
import cn.bestick.util.StringUtil;

/**
 * @author Bestick
 *
 *         be stick...
 *
 *         2015年8月30日 下午7:14:13
 */
public class DataDicDao {

	/**
	 * 判断编号为ddTypeId的DataDic是否存在
	 * 
	 * @param con
	 * @param ddTypeId
	 * @return boolean
	 * @throws Exception
	 */
	public boolean existDataDicByTypeId(Connection con, String ddTypeId)
			throws Exception {
		String sql = "select * from t_dataDic where ddTypeId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, Integer.parseInt(ddTypeId));
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return true;
		} else {
			return false;
		}
	}

	public List<DataDic> dataDicList(Connection con, DataDic s_dataDic,
			PageBean pageBean) throws Exception {
		List<DataDic> dataDicList = new ArrayList<DataDic>();
		StringBuffer sb = new StringBuffer(
				"select * from t_dataDic t1,t_dataDicType t2 where t1.ddTypeId=t2.ddTypeId");
		if (StringUtil.isNotEmpty(s_dataDic.getDdTypeName())) {
			sb.append(" and t2.ddTypeName like '%" + s_dataDic.getDdTypeName()
					+ "%'");
		}
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + ","
					+ pageBean.getPageSize());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			DataDic dataDic = new DataDic();
			dataDic.setDdId(rs.getInt("ddId"));
			dataDic.setDdTypeId(rs.getInt("ddTypeId"));
			dataDic.setDdDesc(rs.getString("ddDesc"));
			dataDic.setDdTypeName(rs.getString("ddTypeName"));
			dataDic.setDdValue(rs.getString("ddValue"));
			dataDicList.add(dataDic);
		}
		return dataDicList;
	}

	public int dataDicCount(Connection con, DataDic s_dataDic) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select count(*) as total from t_dataDic t1,t_dataDicType t2 where t1.ddTypeId=t2.ddTypeId");
		if (StringUtil.isNotEmpty(s_dataDic.getDdTypeName())) {
			sb.append(" and t2.ddTypeName like '%" + s_dataDic.getDdTypeName()
					+ "%'");
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		} else {
			return 0;
		}
	}

	public int dataDicAdd(Connection con, DataDic dataDic) throws Exception {
		String sql = "insert into t_dataDic values(null,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dataDic.getDdTypeId());
		pstmt.setString(2, dataDic.getDdValue());
		pstmt.setString(3, dataDic.getDdDesc());
		return pstmt.executeUpdate();
	}

	public int dataDicUpdate(Connection con, DataDic dataDic) throws Exception {
		String sql = "update t_dataDic set ddTypeId=?,ddValue=?,ddDesc=? where ddId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dataDic.getDdTypeId());
		pstmt.setString(2, dataDic.getDdValue());
		pstmt.setString(3, dataDic.getDdDesc());
		pstmt.setInt(4, dataDic.getDdId());
		return pstmt.executeUpdate();
	}

	public int dataDicDelete(Connection con, int ddId) throws Exception {
		String sql = "delete from t_dataDic where ddId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, ddId);
		return pstmt.executeUpdate();
	}

	public DataDic getDataDicById(Connection con, int ddId) throws Exception {
		DataDic dataDic = null;
		String sql = "select * from t_dataDic t1,t_dataDicType t2 where t1.ddTypeId=t2.ddTypeId and t1.ddId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, ddId);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			dataDic = new DataDic();
			dataDic.setDdId(rs.getInt("ddId"));
			dataDic.setDdTypeId(rs.getInt("ddTypeId"));
			dataDic.setDdTypeName(rs.getString("ddTypeName"));
			dataDic.setDdValue(rs.getString("ddValue"));
			dataDic.setDdDesc(rs.getString("ddDesc"));
		}
		return dataDic;
	}
}
